#encoding: utf-8

load 'tutem_utils.rb'

def channel_basic today = '2022-04-25'

    recs = {}

    sql = "select count(*) as new_users, u.spread_uid as channel_uid, s.real_name as channel_name, s.agency as channel_agency, s.add_time, from_unixtime(s.add_time) as created_at from tutem_mall.eb_user u left join tutem_mall.eb_user s on (u.spread_uid = s.uid)  where u.spread_uid in (select uid from tutem_mall.eb_user where is_promoter = 1 ) group by u.spread_uid ;"

    (queryRds(sql)).each do |q|

        rec = {}
        channel_uid = q['channel_uid']

        #rec.store 'x', q['x']
        rec.store 'channel_uid', q['channel_uid']
        rec.store 'channel_name', q['channel_name']
        rec.store 'channel_agency', q['channel_agency']
        created_at = Time.at(q['add_time']).strftime('%Y-%m-%d')
        rec.store 'created_at', created_at
        actived_days = DateTime.parse(today) - DateTime.parse(created_at)
        rec.store 'actived_days', actived_days
        rec.store 'channel_type', '专业会员'
        rec.store 'new_users', q['new_users']

        recs.store channel_uid, rec if q['new_users'] > 0

    end

    sql = "select count(*) as new_users, u.spread_uid as channel_uid, s.real_name as channel_name, s.agency as channel_agency, s.add_time, from_unixtime(s.add_time) as created_at from tutem_mall.eb_user u left join tutem_mall.eb_user s on (u.spread_uid = s.uid)  where u.spread_uid in (select uid from tutem_mall.eb_user where is_promoter = 0 ) group by u.spread_uid ;"

    (queryRds(sql)).each do |q|

        rec = {}
        channel_uid = q['channel_uid']

        #rec.store 'x', q['x']
        rec.store 'channel_uid', q['channel_uid']
        rec.store 'channel_name', q['channel_name']
        rec.store 'channel_agency', q['channel_agency']
        created_at = Time.at(q['add_time']).strftime('%Y-%m-%d')
        actived_days = DateTime.parse(today) - DateTime.parse(created_at)
        rec.store 'actived_days', actived_days
        rec.store 'created_at', created_at
        rec.store 'channel_type', '普通会员'
        rec.store 'new_users', q['new_users']

        recs.store channel_uid, rec if q['new_users'] > 0

    end

    return recs
end

#计算实际转换购买用户
def attach_buy_info old_recs

    new_recs = {}
    old_recs.each do |channel_uid, rec|

        sql = "select count(*) as paid_users from tutem_mall.eb_user where spread_uid = #{channel_uid} and pay_count > 0;"
        rec.store 'paid_users', 0
        (queryRds(sql)).each do |q|
            rec.store 'paid_users', q['paid_users']
            break
        end

        paid_percentage = 0.00
        paid_percentage = ( (rec['paid_users'] * 100.0 / rec['new_users']) / 100.0 ).to_f.round(2) if rec['new_users'] > 0 
        rec.store 'paid_precentage', paid_percentage

        new_recs.store channel_uid, rec

    end
    return new_recs

end

#首单小于三个标准单位的用户，以及他们后继有购买的比例
def attach_convertion_info old_recs

    new_recs = {}
    old_recs.each do |channel_uid, rec|

        #所有在该渠道
        uids = []
        sql = "select uid from tutem_mall.eb_user where spread_uid = #{channel_uid} and pay_count > 0;"
        (queryRds(sql)).each do |q|
            uids += [q['uid']]
        end

        mickey_uids = []
        tigger_uids = []
        uids.each do |uid|
            sql = "select uid, goods_qty from tutem_mall.eb_pickup_book where uid = #{uid} order by update_time asc limit 1"
            (queryRds(sql)).each do |q|
                if q['goods_qty'] < 3
                    mickey_uids += [q['uid']]
                else
                    tigger_uids += [q['uid']]
                end
            end
        end

        sustained_mickeys = 0
        sql = "select count(*) as sustained_users from tutem_mall.eb_user where uid in (#{mickey_uids.join(',')}) and pay_count > 1" if mickey_uids.size > 0
        (queryRds(sql)).each do |q|
            sustained_mickeys = q['sustained_users']
        end
        
        sustained_tiggers = 0
        sql = "select count(*) as sustained_users from tutem_mall.eb_user where uid in (#{tigger_uids.join(',')}) and pay_count > 1" if tigger_uids.size > 0
        (queryRds(sql)).each do |q|
            sustained_tiggers = q['sustained_users']
        end

        rec.store 'mickey_users', mickey_uids.size
        rec.store 'sustained_mickeys', sustained_mickeys
        mickey_percentage = 0.00
        mickey_percentage = ((rec['sustained_mickeys'] *100 / rec['mickey_users']) / 100.0 ).to_f.round(2) if mickey_uids.size > 0
        rec.store 'sus_mickey_precentage', mickey_percentage

        rec.store 'tigger_users', tigger_uids.size
        rec.store 'sustained_tiggers', sustained_tiggers
        tigger_percentage = 0.00
        tigger_percentage = ((rec['sustained_tiggers'] * 100 / rec['tigger_users']) /100.0 ).to_f.round(2) if tigger_uids.size > 0
        rec.store 'sus_tigger_percentage', tigger_percentage

        new_recs.store channel_uid, rec

    end

    return new_recs

end

today = Time.now.strftime('%Y-%m-%d')
recs = channel_basic today
recs = attach_buy_info recs
recs = attach_convertion_info recs

recs_to_excel recs,"channel-efficiency-all-#{Time.now.to_i}.xls"
